package org.duang.db.sql;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.duang.db.IdEntity;
import org.duang.db.common.Field;
import org.duang.db.common.Order;
import org.duang.db.common.Page;
import org.duang.db.mongo.Operator;
import org.duang.db.sql.parser.SqlDecodeConvetor;
import org.duang.kit.ClassKit;
import org.duang.kit.SqlKit;
import org.duang.kit.ToolsKit;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 仅支持单表操作
 * @author laotang
 *
 */
public class SqlHelper<T> {
	
	private final static Logger logger = LoggerFactory.getLogger(SqlHelper.class);
	private List<Object> querys = null;
	private List<Object> updates = null;
	private Class<T> clazz = null;
	private Order order = null;
	private Field field = null;
	private StringBuilder querySql = null;
	private StringBuilder updateSql = null;
	private Page<T> page = null;
	private int pageNo = 0;
	private int pageSize = 1;
	private final static Object[] OBJ_NULL = new Object[0];
	
	@Deprecated
	public SqlHelper<T> pageNo(int pageNo){
		this.page.setPageNo(pageNo);
        return this;
    }
	@Deprecated
    public SqlHelper<T> pageSize(int pageSize){
    	this.page.pageSize(pageSize);
        return this;
    }

	public SqlHelper(Class<T> cls) {
		this.clazz = cls;
		querys = new ArrayList<Object>();
		updates = new ArrayList<Object>();
//		order = new Order();
//		field = new Field();
		querySql = new StringBuilder();
		updateSql = new StringBuilder();
		page = new Page<T>(pageNo,pageSize);
	}
	
	/**
	 * 等于(=)
	 * @param key			字段名
	 * @param value		内容值
	 * @return
	 * 
	 */
	public SqlHelper<T> eq(String key, Object value) {
		append2Mapping(key, "", value);
		return this;
	}
	
	/**
	 * 大于(>)
	 * @param key			字段名
	 * @param value		内容值
	 * @return
	 */
	public SqlHelper<T> gt(String key, Object value){
		append2Mapping(key, Operator.GT, value);
		return this;
	}
	
	/**
	 *  大于等于(>=)
	  * @param key		字段名
	 * @param value		内容值
	 * @return
	 */
	public SqlHelper<T>gte(String key, Object value){
		append2Mapping(key, Operator.GTE, value);
		return this;
	}
	
	/**
	 * 小于(<)
	 * @param key			字段名
	 * @param value		内容值
	 * @return
	 */
	public SqlHelper<T> lt(String key, Object value){
		append2Mapping(key, Operator.LT, value);
		return this;
	}
	
	/**
	 * 小于等于(<=)
	 * @param key			字段名
	 * @param value		内容值
	 * @return
	 */
	public SqlHelper<T> lte(String key, Object value){
		append2Mapping(key, Operator.LTE, value);
		return this;
	}
	
	/**
	 * 不等于
	 * @param key			字段名
	 * @param value		内容值
	 * @return
	 */
	public SqlHelper<T> ne(String key, Object value){
		append2Mapping(key, Operator.NE, value);
        return this;
	}
	
	/**
	 * or查询
	 * @param key			字段名
	 * @param value		内容值
	 * @return
	 */
	public SqlHelper<T> or(String key, Object value){
		querys.add(value);
		querySql.append(Operator.SQL_OPERATOR_MAP.get(Operator.OR))
			 .append(key)
			 .append("=")
			 .append("?");
        return this;
	}
	
	/**
	 * 模糊查询
	 * @param key			字段名
	 * @param value		内容值
	 * @return
	 */
	public SqlHelper<T> like(String key, Object value){
		querys.add("%"+value+"%");
		querySql//.append(Operator.SQL_OPERATOR_MAP.get(Operator.OR))
		 	 .append(key)
		 	 .append(" like ")
		 	 .append("?");
		return this;
	}
	
	public SqlHelper<T> set(String key, Object value) {
		updates.add(value);
		updateSql.append(key).append("=").append("?,");
		return this;
	}
	
	private void append2Mapping(String key,  String opt, Object value){
		if(ToolsKit.isEmpty(key)) throw new NullPointerException("query key is null or empty...");
		if( null == value) throw new NullPointerException("query value is null...");
		if(IdEntity.ID_FIELD.equals(key)){
			boolean isObjectId = ToolsKit.isValidObjectId((String)value);
			if(!isObjectId) throw new IllegalArgumentException(value + " is not Vaild ObjectId");
		}
		append(key, opt, value);
	}
	
	private void append(String key, String opt, Object value) {
		opt = Operator.SQL_OPERATOR_MAP.get(opt);
		opt = ToolsKit.isEmpty(opt) ? "=" : opt;
		querys.add(value);
		if(querySql.length()  > 0) {
			querySql.append(Operator.SQL_OPERATOR_MAP.get(Operator.AND));
		}
		querySql.append(key)
			 .append(opt)
			 .append("?");
	}
	
	public T findOne() {
		if(ToolsKit.isEmpty(querySql)) throw new NullPointerException("query sql is null");
		if(ToolsKit.isEmpty(querys)) throw new NullPointerException("query params is null");
		List<Map<String,Object>> results = DBSession.query(jointSql(OptEnum.R), querys.toArray());
		if(ToolsKit.isEmpty(results)) return null;
		return SqlDecodeConvetor.convetor(clazz, results.get(0));
	}
	
	public  List<T> findList() {
		List<Map<String,Object>> results = DBSession.query(jointSql(OptEnum.R), querys.toArray());
		if(ToolsKit.isEmpty(results)) return null;
		return SqlKit.toBeans(clazz, results);
	}
	
	private void autoAddStatus(){
		if(querySql.indexOf(IdEntity.STATUS_FIELD) == -1 && 
				!querys.contains(IdEntity.STATUS_FIELD_SUCCESS)){
			eq(IdEntity.STATUS_FIELD, IdEntity.STATUS_FIELD_SUCCESS);
		}
	}
	
	public SqlHelper<T> order(Order order) {
		this.order = order;
		return this;
	}
	
	public SqlHelper<T> page(Page<T> page){
		this.page = page;
		return this;
	}
	
	public Page<T> findPage() {
		if(ToolsKit.isEmpty(page)) throw new NullPointerException("page is null");
		List<Map<String,Object>> results = DBSession.query(jointSql(OptEnum.R), querys.toArray());
		if(ToolsKit.isEmpty(results)) return page;
		List<T> result = SqlKit.toBeans(clazz, results);
		page.setResult(result);
		page.setTotalCount(count());
		return page;
	}
	
	public long count() {
		List<Map<String,Object>> results = DBSession.query(jointSql(OptEnum.T), ToolsKit.isEmpty(querys) ? OBJ_NULL : querys.toArray());
		long count = 0;
		if(ToolsKit.isNotEmpty(results)){
			Map<String, Object> result = results.get(0);
			for(Iterator<Entry<String,Object>> it = result.entrySet().iterator(); it.hasNext();){
				Entry<String,Object> entry = it.next();
				count = (Long)entry.getValue();
			}
		}
		return count;
	}
	
	public int deleteOne() {
		if(ToolsKit.isEmpty(querySql)) throw new NullPointerException("query sql is null");
		if(ToolsKit.isEmpty(querys)) throw new NullPointerException("query params is null");
		return DBSession.execute(jointSql(OptEnum.D), querys.toArray());
	}
	
	public SqlHelper<T> fields(Field field) {
		this.field = field;
		return this;
	}
	
	/**
	 * 	C  create
	 * R	read
	 * 	U	update
	 * D  delete
	 * T  count
	 * @author laotang
	 */
	public enum OptEnum {
		C,R,U,D,T
	}
	
	private String jointSql(OptEnum enums) {
		StringBuilder sql = new StringBuilder();
		if(enums.equals(OptEnum.R)) {
			autoAddStatus();
			sql.append("select ");
			if(ToolsKit.isNotEmpty(field) && ToolsKit.isNotEmpty(field.getFields())){
				for(Iterator<String> it = field.getFields().iterator(); it.hasNext();){
					String columnName = it.next();
					sql.append(columnName).append(",");
				}
				sql.deleteCharAt(sql.length()-1);
			} else {
				sql.append("*");
			}
			sql.append(" from ").append(ClassKit.getBeanName(clazz, true));
			if( querySql.length() > 0 ){	//查询时可以没有where语句
				sql.append(" where ").append(querySql);
			}
			sql.append(getOrderString()).append(getLimitString());
		} else if(enums.equals(OptEnum.U)){
			sql.append("update ")
				 .append(ClassKit.getBeanName(clazz, true))
				 .append(" set ")
				 .append(updateSql.deleteCharAt(updateSql.length()-1))
				 .append(" where ")
				 .append(querySql);
		} else if (enums.equals(OptEnum.D)) {
//			sql.append("delete from ").append(ClassKit.getBeanName(clazz, true)).append(" where ").append(querySql);
			sql.append("update ")
			 .append(ClassKit.getBeanName(clazz, true))
			 .append(" set ").append(IdEntity.STATUS_FIELD).append("=").append("'").append(IdEntity.STATUS_FIELD_DELETE).append("'")
			 .append(" where ")
			 .append(updateSql.deleteCharAt(updateSql.length()-1));
		} else if (enums.equals(OptEnum.T)) {
			sql.append("select count(*) from ").append(ClassKit.getBeanName(clazz, true));
			if(querySql.length()>0){
				sql.append(" where ").append(querySql);
			}
		}
//		querySql.delete(0, querySql.length());
//		updateSql.delete(0, updateSql.length());
		return sql.toString();
	}
	
	public int update() {
		if(ToolsKit.isEmpty(querySql)) throw new NullPointerException("query sql is null");
		if(ToolsKit.isEmpty(querys)) throw new NullPointerException("query params is null");
		if(ToolsKit.isEmpty(updateSql)) throw new NullPointerException("update sql is null");
		if(ToolsKit.isEmpty(updates)) throw new NullPointerException("update params is null");
		updates.addAll(querys);
		return DBSession.execute(jointSql(OptEnum.U), updates.toArray());
	}
	
	private String getOrderString() {
		if(ToolsKit.isEmpty(order)) return "";
		Map<String, String> map = order.getOrder();
		StringBuilder orderSql = new StringBuilder();
		for(Iterator<Entry<String,String>> it = map.entrySet().iterator(); it.hasNext();){
			Entry<String,String> entry = it.next();
			orderSql.append(entry.getKey()).append(" ").append(entry.getValue()).append(",");
		}
		if(orderSql.length()>0){
			orderSql.deleteCharAt(orderSql.length()-1);
		}
		return " order by " + orderSql.toString();
	}
	
	private String getLimitString() {
		if(ToolsKit.isNotEmpty(page)){
			pageNo = page.getPageNo() == 1 ? 0 : page.getPageNo();
			pageNo = (pageNo>0) ? pageNo-1 : pageNo;
			pageSize = page.getPageSize();
		} 
		if(pageNo == 0 && pageSize == 1) {
			return "";
		}
		StringBuilder limitSql = new StringBuilder();
		limitSql.append(" limit ").append(pageNo*pageSize).append(",").append(pageSize);
		return limitSql.toString();
	}
	
	
//	/**
//	 * 返回sql查询语句，仅支持单表查询
//	 * @return
//	 */
//	public static SqlObject getQuerySqlObj(Query query) {
//		DBObject queryObj = query.getQueryObj();
//		System.out.println("@@@@@@@: " + queryObj.toString());
//		StringBuilder querySql = new StringBuilder();
//		List<Object> params = new ArrayList<Object>();
//		for(Iterator<String> it = queryObj.keySet().iterator(); it.hasNext();){
//			String key = it.next();
//			Object dbo = queryObj.get(key);
//			System.out.println(key+"          "+dbo.getClass().getSimpleName());
//			if(dbo instanceof DBObject) {
//				SqlObject sqlObj = dbObject2SqlObject(key, (DBObject)dbo, "");
//				if(ToolsKit.isNotEmpty(sqlObj)){
//					querySql.append(sqlObj.getSql());
//					params.addAll(sqlObj.getParams());
//				}
//			} else if(dbo instanceof Collection){
//				String andOrString = " or ";
//				for(Iterator<Collection> itemIt = ((Collection) dbo).iterator(); itemIt.hasNext();){
//					DBObject tmpDbo = (DBObject)itemIt.next();
//					for(Iterator<String> tmpIt = tmpDbo.keySet().iterator(); tmpIt.hasNext();){
//						String tmpKey = tmpIt.next();
//						SqlObject sqlObj =dbObject2SqlObject(tmpKey, tmpDbo, andOrString);
//						if(ToolsKit.isNotEmpty(sqlObj)){
//							System.out.println("sqlObj: " + sqlObj);
//							System.out.println("querySql: " + querySql);
//							if(querySql.length() > 0 && querySql.toString().endsWith(" and ")){
//								querySql.delete(querySql.length()-5, querySql.length());
//								querySql.append(andOrString);
//							}
//							System.out.println("querySql: " + querySql);
//							querySql.append(sqlObj.getSql());
//							params.addAll(sqlObj.getParams());
//						}
//					}
//				}
//			} else {
//				querySql.append(key).append(" = ? ").append(" and ");
//				params.add(dbo);
//			}
//		}
//		if(ToolsKit.isNotEmpty(querySql)){
//			querySql.delete(querySql.length()-4, querySql.length());
//		}
//		logger.debug(" query: " + querySql.toString()+"         params: " + params);
//		return new SqlObject(querySql.toString(), params);
//	}
//	
//	private static SqlObject dbObject2SqlObject(String key, DBObject dbo, String andOrString) {
//		System.out.println("##########key: "+ key);
//		System.out.println("##########dbo: "+ dbo);
//		if(ToolsKit.isEmpty(andOrString)) andOrString = " and ";
//		SqlObject sqlObj = new SqlObject();
//		StringBuilder sql = new StringBuilder();
//		List<Object> parmas = new ArrayList<Object>();
//		for(Iterator<String> it = dbo.keySet().iterator(); it.hasNext();){
//			String dboKey = it.next();
//			String opt = Operator.SQL_OPERATOR_MAP.get(dboKey);
//			if(ToolsKit.isEmpty(opt)){
//				opt = " = ";
//			}
//			sql.append(key).append(opt).append("?").append(andOrString);
//			parmas.add(dbo.get(dboKey));
//		}
//		sqlObj.setSql(sql.toString());
//		sqlObj.setParams(parmas);
//		System.out.println(sqlObj.getSql()+"           "+ToolsKit.toJsonString(sqlObj.getParams()));
//		return ToolsKit.isEmpty(sqlObj.getSql()) ? null : sqlObj;
//		
//	}

	
}
